﻿using CmccTool.KPI.Services;
using CmccTool.Services;
using SQLite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Eventing.Reader;
using System.Globalization;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Text.Json.Serialization;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using static CmccTool.KPI.DbTables.KpiTables;
using static System.Runtime.InteropServices.JavaScript.JSType;


// 基于sqlite-net-pcl
namespace CmccTool.KPI.DbTables
{
    internal partial class KpiTables
    {
        #region 拓展方法

        /// <summary>
        /// 转换为安全的 double 值，失败时返回默认值，返回值不可为null
        /// </summary>
        /// <param name="value">待转值</param>
        /// <param name="defaultValue">默认值</param>
        /// <returns></returns>
        private static double SafeParseDouble(object value, double defaultValue = 0)
            => double.TryParse(value?.ToString(), out double result) ? result : defaultValue;

        /// <summary>
        /// 转换为安全的 double 值,失败时返回空值
        /// </summary>
        /// <param name="value">待转值</param>
        /// <returns></returns>
        private static double? SafeParseDoubleNull(object value)
            => double.TryParse(value?.ToString(), out double result) ? result : null;


        /// <summary>
        /// 从日期范围字符串中提取起始和结束日期
        /// </summary>
        /// <param name="dateRange">日期范围</param>
        /// <returns></returns>
        private static (string dateStart, string dateEnd) GetDateFromDateRange(string dateRange)
        {
            // 0. 定义无效时的默认返回值
            const string InvalidDate = "1990-01-01";

            // 1. 检查输入是否为空或无效分隔符
            if (string.IsNullOrEmpty(dateRange) || !dateRange.Contains('~'))
            {
                return (InvalidDate, InvalidDate); // 返回固定无效标记
            }

            // 2. 分割日期范围字符串
            string[] dateParts = dateRange.Split('~');
            if (dateParts.Length < 2)
            {
                return (InvalidDate, InvalidDate);
            }

            // 3. 解析起始日期和结束日期
            string startDateString = dateParts[0].Trim();
            string endDateString = dateParts[1].Trim();

            // 4. 尝试解析为 DateTime 对象（格式：yyyyMMdd）
            bool isStartValid = DateTime.TryParseExact(
                startDateString,
                "yyyyMMdd",
                CultureInfo.InvariantCulture,
                DateTimeStyles.None,
                out DateTime startDate
            );

            bool isEndValid = DateTime.TryParseExact(
                endDateString,
                "yyyyMMdd",
                CultureInfo.InvariantCulture,
                DateTimeStyles.None,
                out DateTime endDate
            );

            // 5. 返回结果：任一解析失败则返回固定无效值
            return isStartValid && isEndValid
                ? (startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"))
                : (InvalidDate, InvalidDate);
        }

        /// <summary>
        /// 根据CGI信息提取ENI基站号和Sys系统信息
        /// </summary>
        /// <param name="CGI"></param>
        /// <returns></returns>
        private static (string ENI, string Sys) GetInfoFormCgi(string? CGI)
        {
            if (string.IsNullOrEmpty(CGI))
                return (string.Empty, string.Empty);

            if (!CGI.Contains('-')) { return (string.Empty, string.Empty); }

            string[] parts = CGI.Split('-');
            // 确保分割后至少有 3 个元素（索引 0、1、2 均存在）
            string ENI = parts.Length >= 3 ? parts[2] : string.Empty;
            string Sys;

            if (ENI.Length == 4)
            {
                Sys = "2G";
            }
            else if (ENI.Length == 7)
            {
                Sys = "5G";
            }
            else
            {
                Sys = "4G";
            }

            return (ENI, Sys);
        }


        private static string GetCgiFromCellname(string cellname)
        {
            //Match match = Regex.Match(cellname, @"[A-Za-z]+(\d+)([A-Za-z])(\d)?");
            Match match = CellNameToCgiRegex().Match(cellname);
            string CGI = "460-00-";
            int CellId;
            string CELL;
            string Sec;
            if (match.Success)
            {
                if (!int.TryParse(match.Groups[1].Value, out int ENI)) { return string.Empty; }
                CELL = match.Groups[2].Value;
                Sec = match.Groups[3].Value;
                /*
                if (string.IsNullOrEmpty(Sec)) { CellId = 0; }
                else if(Sec == "1") { CellId = 32; }
                else if(Sec == "2") { CellId = 48; }
                */
                CellId = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".IndexOf(CELL);
                CellId += Sec switch
                {
                    "1" => 32,
                    "2" => 48,
                    _ => 0   // 默认值（包括 null、空字符串及其他值）
                };

                CellId += ENI switch
                {
                    // 使用关系模式 + 逻辑组合符（and/or）
                    (>= 94208 and <= 98303) or (>= 155648 and <= 159743) => 1,
                    (>= 897024 and <= 901119) or (>= 356352 and <= 360447) => 129,
                    (>= 225280 and <= 229375) or (>= 229376 and <= 233471) or (>= 237568 and <= 241663) => 65,
                    (>= 407424 and <= 489740) => 193,
                    _ => 0  // 默认分支
                };

                CGI += ENI.ToString() + "-" + CellId.ToString();
            }
            else
            {                 // 如果没有匹配到，返回空字符串
                return string.Empty;
            }

            return CGI;
        }

        #endregion

        #region 正则定义
        [GeneratedRegex(@"[A-Za-z]+(\d+)([A-Za-z])(\d)?")]
        public static partial Regex CellNameToCgiRegex();

        [GeneratedRegex(@"[A-Za-z]+")]
        public static partial Regex RegexGetAz();


        [GeneratedRegex(@"\d{5,7}")]
        public static partial Regex RegexGetEni();

        #endregion

        #region Kpi表格定义
        // OMC指标数据
        [Table("KpiOmc")]
        public sealed record KpiOmc : KpiImpHelper.ICsvEntityFactory<KpiOmc>
        {
            [PrimaryKey]
            public required string KeyId { get; init; }
            public required string Sys { get; init; }
            [Indexed] public required string Date { get; init; }
            public required string CellName { get; init; }
            [Column("工参小区")] public string? GcCellName { get; init; }
            [Column("工参基站")] public string? GcNodeName { get; init; }
            [Indexed] public string? CGI { get; init; }
            [Indexed] public string? ENI { get; init; }
            [Column("接通率")] public double? RadioSuccRate { get; init; }
            [Column("掉线率")] public double? RadioDropRate { get; init; }
            [Column("切换成功数")] public double? HoSucc { get; init; }
            [Column("切换总数")] public double? HoTotal { get; init; }
            [Column("切换成功率")] public double? HoRate { get; init; }
            [Column("语音上丢包率")] public double? LostRateULQci1 { get; init; }
            [Column("语音下丢包率")] public double? LostRateDLQci1 { get; init; }
            [Column("语音丢包率")] public double? LostRateQci1 { get; init; }
            [Column("语音丢包数")] public double? LostPackageQci1 { get; init; }
            [Column("用户数")] public int? User { get; init; }
            public double? GB_Up { get; init; }
            public double? GB_Down { get; init; }
            public double? GB { get; init; }
            public double? RSSI { get; init; }
            public int IsNew { get; init; }
            [Column("经分网格")] public string? Jfwg { get; init; }
            [Column("经分分局")] public string? JfFj { get; init; }
            [Column("归属乡镇")] public string? Xz { get; init; }

            [JsonConstructor] // 支持 JSON 反序列化

            // 无参数构造函数
            public KpiOmc() { }

            public static (KpiOmc, bool) CreateFromRow(Dictionary<string, string> row)
            {
                bool isValid = true;
                if (!row.TryAdd("日期", "2000-01-01"))
                {
                    if (!DateTime.TryParse(row["日期"], out _))
                    {
                        row["日期"] = "2000-01-01";
                        isValid = false;
                    }
                }
                else
                {
                    isValid = false;
                }
                // 完善字段
                string date = DateTime.Parse(row["日期"]).ToString("yyyy-MM-dd");
                object Fields = new[] { "CellName", "LostPackageULQci1", "LostPackageDLQci1", "AllPackageULQci1", "AllPackageDLQci1",
                    "HoSucc1", "HoSucc2", "HoSucc3", "HoSucc4", "HoTotal1", "HoTotal2", "HoTotal3", "HoTotal4",
                    "RadioSuccRate", "RadioDropRate", "LostRateULQci1", "LostRateDLQci1",
                    "User", "GB_Up", "GB_Down", "RSSI" };
                if (Fields is IEnumerable<string> fields)
                {
                    foreach (var field in fields)
                    {
                        // 若字典不包含当前字段，则添加空字符串
                        row.TryAdd(field, string.Empty);
                    }
                }
                // 获取ENI 和 Sys
                string CellName = row["CellName"];
                if (string.IsNullOrEmpty(CellName)) { isValid = false; }
                string Cgi;
                (string ENI, string Sys) CgiInfo;
                if (CellName.Contains('-'))
                {
                    //Cgi = Regex.Replace(CellName, @"[A-Za-z]+", "460-00-");
                    Cgi = RegexGetAz().Replace(CellName, "460-00-");
                    CgiInfo = GetInfoFormCgi(Cgi);

                    string[] parts = CellName.Split('-');
                    CellName = parts[0];

                    int CellId = int.TryParse(parts[1], out int cellId) ? cellId : 0;
                    CellName += "ABCDEFGHIJKLMNOPQRSTUVWXYZ?".Substring((CellId > 0 && CellId < 27) ? CellId - 1 : 26, 1);

                }
                else
                {
                    // 1. 使用 Regex.Match 获取匹配结果（返回 Match 对象）
                    //Match match = Regex.Match(CellName, @"\d{5,6}");
                    Match match = RegexGetEni().Match(CellName);
                    // 2. 提取匹配的字符串（需检查匹配是否成功）
                    string ENI = match.Success ? match.Value : string.Empty;

                    CgiInfo = (ENI, "4G");

                    Cgi = GetCgiFromCellname(CellName);
                }
                //
                isValid = isValid == true && CellName.Contains(AppConfig.InsertCompanyOmc, StringComparison.CurrentCultureIgnoreCase);

                // 中间处理
                string keyId = $"{date}_{Cgi}";
                double LostPackageQci1 = (double)(SafeParseDouble(row["LostPackageULQci1"]) + SafeParseDouble(row["LostPackageDLQci1"]));
                double AllPackageQci1 = SafeParseDouble(row["AllPackageULQci1"]) + SafeParseDouble(row["AllPackageDLQci1"]);
                double HoSucc = SafeParseDouble(row["HoSucc1"]) + SafeParseDouble(row["HoSucc2"]) + SafeParseDouble(row["HoSucc3"]) + SafeParseDouble(row["HoSucc4"]);
                double HoTotal = SafeParseDouble(row["HoTotal1"]) + SafeParseDouble(row["HoTotal2"]) + SafeParseDouble(row["HoTotal3"]) + SafeParseDouble(row["HoTotal4"]);
                double RadioSuccRate = SafeParseDouble(row["RadioSuccRate"]);
                double RadioDropRate = SafeParseDouble(row["RadioDropRate"]);
                double LostRateULQci1 = SafeParseDouble(row["LostRateULQci1"]);
                double LostRateDLQci1 = SafeParseDouble(row["LostRateDLQci1"]);


                // 返回一个新的 KpiOmc 实例
                return (new KpiOmc
                {
                    KeyId = keyId,
                    Sys = CgiInfo.Sys,
                    Date = date,
                    CellName = CellName,
                    CGI = Cgi,
                    ENI = CgiInfo.ENI,
                    RadioSuccRate = RadioSuccRate != 0 ? Math.Round(RadioSuccRate, 2) : null,
                    RadioDropRate = RadioDropRate != 0 ? Math.Round(RadioDropRate, 2) : null,
                    HoSucc = Math.Round(HoSucc, 2),
                    HoTotal = Math.Round(HoTotal, 2),
                    HoRate = HoTotal != 0 ? Math.Round(HoSucc * 100.0 / HoTotal, 2) : null,
                    LostRateULQci1 = LostRateULQci1 != 0 ? Math.Round(LostRateULQci1, 2) : null,
                    LostRateDLQci1 = LostRateDLQci1 != 0 ? Math.Round(LostRateDLQci1, 2) : null,
                    LostPackageQci1 = Math.Round(LostPackageQci1, 2),
                    LostRateQci1 = AllPackageQci1 != 0 ? Math.Round(LostPackageQci1 * 100 / AllPackageQci1, 2) : null,
                    User = (int)SafeParseDouble(row["User"]),
                    GB_Up = Math.Round(SafeParseDouble(row["GB_Up"]), 4),
                    GB_Down = Math.Round(SafeParseDouble(row["GB_Down"]), 4),
                    GB = Math.Round((SafeParseDouble(row["GB_Up"]) + SafeParseDouble(row["GB_Down"])), 4),
                    RSSI = Math.Round(SafeParseDouble(row["RSSI"]), 2),
                    IsNew = 1,
                }, isValid);
            }
        }

        // 新网优平台数据
        [Table("KpiXwy")]
        public sealed record KpiXwy : KpiImpHelper.ICsvEntityFactory<KpiXwy>
        {
            [PrimaryKey]
            public required string KeyId { get; init; }
            public required string Sys { get; init; }
            [Indexed] public required string Date { get; init; }
            public required string CellName { get; init; }
            public required string NodeName { get; init; }
            [Column("工参小区")] public string? GcCellName { get; init; }
            [Column("工参基站")] public string? GcNodeName { get; init; }
            [Column("公司")] public required string Company { get; init; }
            [Column("厂家")] public required string Factory { get; init; }
            [Column("场景")] public string? Scene { get; init; }
            [Indexed] public required string CGI { get; init; }
            [Indexed] public required string ENI { get; init; }
            public int User { get; init; }
            public double Erl { get; init; }
            double RSSI { get; init; }
            public double MB_Up { get; init; }
            public double MB_Down { get; init; }
            public double GB { get; init; }
            public double PU { get; init; }
            public int IsNew { get; init; }
            [Column("经分网格")] public string? Jfwg { get; init; }
            [Column("经分分局")] public string? JfFj { get; init; }
            [Column("归属乡镇")] public string? Xz { get; init; }


            [JsonConstructor] // 支持 JSON 反序列化

            // 无参数构造函数
            public KpiXwy() { }

            // 工厂接口实现
            public static (KpiXwy, bool) CreateFromRow(Dictionary<string, string> row)
            {
                bool isValid = true;
                if (!row.TryAdd("日期", "2000-01-01"))
                {
                    if (!DateTime.TryParse(row["日期"], out _))
                    {
                        row["日期"] = "2000-01-01";
                        isValid = false;
                    }
                }
                else
                {
                    isValid = false;
                }
                string date = DateTime.Parse(row["日期"]).ToString("yyyy-MM-dd");
                // 完善字段
                object Fields = new[] { "基站名称", "小区名称", "分公司", "厂家", "场景", "CGI", "RRC连接最大数", "ERL", "RSSI", "MB_Up", "MB_Down", "PU" };
                if (Fields is IEnumerable<string> fields)
                {
                    foreach (var field in fields)
                    {
                        // 若字典不包含当前字段，则添加空字符串
                        row.TryAdd(field, string.Empty);
                    }
                }
                // 兼容2G

                (string ENI, string Sys) CgiInfo;
                if (row.TryGetValue("TAC", out string? value))
                {
                    string ENI = row["CGI"][..4];
                    row["CGI"] = "460-00-" + value + "-" + ENI + "-" + row["CGI"].Substring(4, 1);
                    CgiInfo = (ENI, "2G");
                }
                else
                {
                    CgiInfo = GetInfoFormCgi(row["CGI"]);
                }

                string Cgi = row["CGI"];
                if (string.IsNullOrEmpty(Cgi)) { isValid = false; }
                string keyId = $"{date}_{Cgi}";

                double MB_Up = SafeParseDouble(row["MB_Up"]);
                double MB_Down = SafeParseDouble(row["MB_Down"]);

                // 校验导入公司
                string Company = row["分公司"];
                isValid = isValid && (AppConfig.InsertCompany == string.Empty || AppConfig.InsertCompany == Company);

                return (new KpiXwy
                {
                    KeyId = keyId,
                    Sys = CgiInfo.Sys switch
                    {
                        "NR" => "5G",
                        "LTE" => "4G",
                        _ => CgiInfo.Sys
                    },
                    Date = date,
                    CellName = row["小区名称"],
                    NodeName = row["基站名称"],
                    Company = Company,
                    Factory = row["厂家"],
                    Scene = row["场景"],
                    CGI = Cgi,
                    ENI = CgiInfo.ENI,
                    User = (int)SafeParseDouble(row["RRC连接最大数"]),
                    Erl = Math.Round(SafeParseDouble(row["ERL"]), 2),
                    RSSI = Math.Round(SafeParseDouble(row["RSSI"]), 2),
                    MB_Up = MB_Up,
                    MB_Down = MB_Down,
                    GB = Math.Round((MB_Up + MB_Down) / 1024.0, 4),
                    PU = SafeParseDouble(row["PU"].Replace("%", "")),
                    IsNew = 1,
                }, isValid);
            }
        }

        // 4G小区零低数据
        [Table("KpiXwyLteLd")]
        public sealed record KpiXwyLteLd : KpiImpHelper.ICsvEntityFactory<KpiXwyLteLd>
        {
            [PrimaryKey]
            public required string KeyId { get; init; }

            [Indexed] public required string Date { get; init; }
            public required string DateRange { get; init; }
            [Column("公司")] public required string Company { get; init; }
            [Column("厂家")] public required string Factory { get; init; }
            [Column("场景")] public string? Scene { get; init; }
            [Column("小区名")] public required string CellName { get; init; }
            [Column("质差类型")] public required string ProblemType { get; init; }
            [Indexed] public required string CGI { get; init; }
            [Indexed] public required string ENI { get; init; }
            public double MB { get; init; }
            public int IsSchool { get; init; }

            // 无参数构造函数
            public KpiXwyLteLd() { }


            // 工厂接口实现
            public static (KpiXwyLteLd, bool) CreateFromRow(Dictionary<string, string> row)
            {
                bool isValid;

                // 完善字段
                object Fields = new[] { "日期范围", "分公司", "厂家", "场景", "小区名", "CGI", "流量MB", "是否校园" };
                if (Fields is IEnumerable<string> fields)
                {
                    foreach (var field in fields)
                    {
                        // 若字典不包含当前字段，则添加空字符串
                        row.TryAdd(field, string.Empty);
                    }
                }

                // 校验导入公司
                string Company = row["分公司"];
                isValid = AppConfig.InsertCompany == string.Empty || AppConfig.InsertCompany == Company;


                string dateRange = row["日期范围"];
                string date = GetDateFromDateRange(dateRange).dateEnd;
                if (SafeParseDouble(date[..4]) <= 2000) { isValid = false; }
                string keyId = date + "-" + row["CGI"];
                var CgiInfo = GetInfoFormCgi(row["CGI"]);
                double MB = Math.Round(SafeParseDouble(row["流量MB"]), 2);
                return (new KpiXwyLteLd
                {
                    KeyId = keyId,
                    Date = date,
                    DateRange = dateRange,
                    Company = row["分公司"],
                    Factory = row["厂家"],
                    Scene = row["场景"],
                    CellName = row["小区名"],
                    CGI = row["CGI"],
                    ENI = CgiInfo.ENI,
                    MB = MB,
                    ProblemType = MB == 0 ? "零流量" : "低流量",
                    IsSchool = row["是否校园"] == "是" ? 1 : 0,

                }, isValid);
            }

        }


        // 5G小区零低数据
        [Table("KpiXwyNrLd")]
        public sealed record KpiXwyNrLd : KpiImpHelper.ICsvEntityFactory<KpiXwyNrLd>
        {
            [PrimaryKey]
            public required string KeyId { get; init; }

            [Indexed] public required string Date { get; init; }
            public required string DateRange { get; init; }
            [Column("公司")] public required string Company { get; init; }
            [Column("厂家")] public required string Factory { get; init; }
            [Column("场景")] public string? Scene { get; init; }
            [Column("小区名")] public required string CellName { get; init; }
            [Column("工作频段")] public required string WorkRange { get; init; }
            [Column("基站类型")] public required string NodeType { get; init; }
            [Column("质差类型")] public required string ProblemType { get; init; }
            [Indexed] public required string CGI { get; init; }
            [Indexed] public required string ENI { get; init; }
            public double? MBDay6 { get; init; }
            public double? MBDay5 { get; init; }
            public double? MBDay4 { get; init; }
            public double? MBDay3 { get; init; }
            public double? MBDay2 { get; init; }
            public double? MBDay1 { get; init; }
            public double? MBDay0 { get; init; }
            public double? MB { get; init; }
            public int IsSchool { get; init; }
            public int IsHighIdsRSSI { get; init; } // 是否IDS采集高干扰小区

            // 无参数构造函数
            public KpiXwyNrLd() { }


            // 工厂接口实现
            public static (KpiXwyNrLd, bool) CreateFromRow(Dictionary<string, string> row)
            {
                bool isValid;

                // 完善字段
                object Fields = new[] { "日期范围", "分公司", "厂家", "场景", "小区名", "工作频段", "CGI", "是否校园", "基站类型", "质差类型", "是否IDS采集高干扰小区", "MBDay6", "MBDay5", "MBDay4", "MBDay3", "MBDay2", "MBDay1", "MBDay0" };
                if (Fields is IEnumerable<string> fields)
                {
                    foreach (var field in fields)
                    {
                        // 若字典不包含当前字段，则添加空字符串
                        row.TryAdd(field, string.Empty);
                    }
                }

                // 校验导入公司
                string Company = row["分公司"];
                isValid = AppConfig.InsertCompany == string.Empty || AppConfig.InsertCompany == Company;


                string dateRange = row["日期范围"];
                string date = GetDateFromDateRange(dateRange).dateEnd;
                if (SafeParseDouble(date[..4]) <= 2000) { isValid = false; }
                string keyId = date + "-" + row["CGI"];
                var CgiInfo = GetInfoFormCgi(row["CGI"]);

                double? MBDay6 = SafeParseDoubleNull(row["MBDay6"]);
                double? MBDay5 = SafeParseDoubleNull(row["MBDay5"]);
                double? MBDay4 = SafeParseDoubleNull(row["MBDay4"]);
                double? MBDay3 = SafeParseDoubleNull(row["MBDay3"]);
                double? MBDay2 = SafeParseDoubleNull(row["MBDay2"]);
                double? MBDay1 = SafeParseDoubleNull(row["MBDay1"]);
                double? MBDay0 = SafeParseDoubleNull(row["MBDay0"]);

                double MBSum = 0;
                double DayCount = 0;
                for (int i = 0; i <= 6; i++)
                {
                    var MB = SafeParseDoubleNull(row["MBDay" + i]);
                    if (MB != null)
                    {
                        MBSum += (double)MB;
                        DayCount += 1;
                    }
                }

                return (new KpiXwyNrLd
                {
                    KeyId = keyId,
                    Date = date,
                    DateRange = dateRange,
                    Company = Company,
                    Factory = row["厂家"],
                    Scene = row["场景"],
                    CellName = row["小区名"],
                    WorkRange = row["工作频段"],
                    CGI = row["CGI"],
                    NodeType = row["基站类型"],
                    ProblemType = row["质差类型"],
                    MBDay6 = MBDay6,
                    MBDay5 = MBDay5,
                    MBDay4 = MBDay4,
                    MBDay3 = MBDay3,
                    MBDay2 = MBDay2,
                    MBDay1 = MBDay1,
                    MBDay0 = MBDay0,
                    ENI = CgiInfo.ENI,
                    MB = DayCount > 0 ? Math.Round(MBSum / DayCount, 2) : null,
                    IsSchool = row["是否校园"] == "是" ? 1 : 0,
                    IsHighIdsRSSI = row["是否IDS采集高干扰小区"] == "是" ? 1 : 0,
                }, isValid);
            }

        }

        // 5G小区低效
        [Table("KpiXwyNrDx")]
        public sealed record KpiXwyNrDx : KpiImpHelper.ICsvEntityFactory<KpiXwyNrDx>
        {
            [PrimaryKey]
            public required string KeyId { get; init; }

            [Indexed] public required string Date { get; init; }
            public required string DateRange { get; init; }
            [Column("公司")] public required string Company { get; init; }
            [Column("厂家")] public required string Factory { get; init; }
            [Column("场景")] public string? Scene { get; init; }
            [Indexed][Column("小区名")] public required string CellName { get; init; }
            [Column("工作频段")] public required string WorkRange { get; init; }
            [Column("基站类型")] public required string NodeType { get; init; }
            [Column("质差类型")] public required string ProblemType { get; init; }
            [Indexed] public required string CGI { get; init; }
            [Indexed] public required string ENI { get; init; }
            public double? MBDay6 { get; init; }
            public double? MBDay5 { get; init; }
            public double? MBDay4 { get; init; }
            public double? MBDay3 { get; init; }
            public double? MBDay2 { get; init; }
            public double? MBDay1 { get; init; }
            public double? MBDay0 { get; init; }
            public double? GbAvgMax3 { get; init; }
            public int IsSchool { get; init; }
            public int IsHighIdsRSSI { get; init; } // 是否IDS采集高干扰小区
            public int IsWhite { get; init; }

            // 无参数构造函数
            public KpiXwyNrDx() { }


            // 工厂接口实现
            public static (KpiXwyNrDx, bool) CreateFromRow(Dictionary<string, string> row)
            {
                bool isValid;

                // 完善字段
                object Fields = new[] { "日期范围", "分公司", "厂家", "场景", "小区名", "工作频段", "CGI", "是否校园", "基站类型", "质差类型", "是否IDS采集高干扰小区", "MBDay6", "MBDay5", "MBDay4", "MBDay3", "MBDay2", "MBDay1", "MBDay0", "GbAvgMax3", "是否白名单剔除" };
                if (Fields is IEnumerable<string> fields)
                {
                    foreach (var field in fields)
                    {
                        // 若字典不包含当前字段，则添加空字符串
                        row.TryAdd(field, string.Empty);
                    }
                }

                // 校验导入公司
                string Company = row["分公司"];
                isValid = AppConfig.InsertCompany == string.Empty || AppConfig.InsertCompany == Company;


                string dateRange = row["日期范围"];
                string date = GetDateFromDateRange(dateRange).dateEnd;
                if (SafeParseDouble(date[..4]) <= 2000) { isValid = false; }
                string keyId = date + "-" + row["CGI"];
                var CgiInfo = GetInfoFormCgi(row["CGI"]);

                double? MBDay6 = SafeParseDoubleNull(row["MBDay6"]);
                double? MBDay5 = SafeParseDoubleNull(row["MBDay5"]);
                double? MBDay4 = SafeParseDoubleNull(row["MBDay4"]);
                double? MBDay3 = SafeParseDoubleNull(row["MBDay3"]);
                double? MBDay2 = SafeParseDoubleNull(row["MBDay2"]);
                double? MBDay1 = SafeParseDoubleNull(row["MBDay1"]);
                double? MBDay0 = SafeParseDoubleNull(row["MBDay0"]);



                return (new KpiXwyNrDx
                {
                    KeyId = keyId,
                    Date = date,
                    DateRange = dateRange,
                    Company = Company,
                    Factory = row["厂家"],
                    Scene = row["场景"],
                    CellName = row["小区名"],
                    WorkRange = row["工作频段"],
                    CGI = row["CGI"],
                    NodeType = row["基站类型"],
                    ProblemType = row["质差类型"],
                    MBDay6 = MBDay6,
                    MBDay5 = MBDay5,
                    MBDay4 = MBDay4,
                    MBDay3 = MBDay3,
                    MBDay2 = MBDay2,
                    MBDay1 = MBDay1,
                    MBDay0 = MBDay0,
                    ENI = CgiInfo.ENI,
                    GbAvgMax3 = SafeParseDouble(row["GbAvgMax3"]),
                    IsSchool = row["是否校园"] == "是" ? 1 : 0,
                    IsHighIdsRSSI = row["是否IDS采集高干扰小区"] == "是" ? 1 : 0,
                    IsWhite = row["是否白名单剔除"] == "是" ? 1 : 0,
                }, isValid);
            }

        }
        #endregion

        #region 工参信息
        [Table("GcCgiInfo")]
        public class GcCgiInfo : KpiImpHelper.ICsvEntityFactory<GcCgiInfo>
        {
            [PrimaryKey]
            [Indexed] public string CGI { get; set; } = null!;
            [Indexed] public string ENI { get; set; } = null!;
            [Column("工参小区")] public string? GcCellName { get; set; }
            [Column("工参基站")] public string? GcNodeName { get; set; }

            // 无参数构造函数
            public GcCgiInfo() { }

            // 工厂接口实现
            public static (GcCgiInfo, bool) CreateFromRow(Dictionary<string, string> row)
            {
                bool isValid;

                // 完善字段
                object Fields = new[] { "CGI", "ENI", "工参小区", "工参基站" };
                if (Fields is IEnumerable<string> fields)
                {
                    foreach (var field in fields)
                    {
                        // 若字典不包含当前字段，则添加空字符串
                        row.TryAdd(field, string.Empty);
                    }
                }

                // 校验导入公司
                string CGI = row["CGI"] ?? string.Empty;
                string ENI = row["ENI"] ?? string.Empty;
                string GcCellName = row["工参小区"] ?? string.Empty;
                string GcNodeName = row["工参基站"] ?? string.Empty;
                if (string.IsNullOrEmpty(CGI) || string.IsNullOrEmpty(ENI) || string.IsNullOrEmpty(GcCellName) || string.IsNullOrEmpty(GcNodeName))
                {
                    isValid = false;
                }
                else
                {
                    isValid = true;
                }

                return (new GcCgiInfo
                {
                    CGI = CGI,
                    ENI = ENI,
                    GcCellName = GcCellName,
                    GcNodeName = GcNodeName

                }, isValid);
            }
        }
        #endregion

        #region 问题明细 、质差小区 、 关联表
        [Table("ErrNode")]
        public sealed class ErrNode
        {
            [PrimaryKey]
            [Indexed] public string KeyId { get; set; } = null!;
            [Indexed] public string ENI { get; set; } = null!;
            [Column("工参基站")] public string? GcNodeName { get; set; }
            [Indexed] public string? CGI { get; set; }
            [Indexed][Column("开始日期")] public string StartDate { get; set; } = null!;
            [Column("问题分析")] public string Problem { get; set; } = null!;
            [Column("问题一级")] public string ProblemLevel1 { get; set; } = null!;
            [Column("问题二级")] public string ProblemLevel2 { get; set; } = null!;
            [Column("措施分类")] public string MeasureType { get; set; } = null!;
            [Column("处理措施")] public string? Measure { get; set; }
            [Column("责任室")] public string ResponsibleDept { get; set; } = null!;
            [Column("责任专业")] public string ResponsibleSpecialty { get; set; } = null!;
            [Column("责任人")] public string ResponsiblePerson { get; set; } = null!;
            [Column("处理日期")] public string? ProcessTime { get; set; }
            [Column("状态")] public string Status { get; set; } = null!;
            [Column("确认日期")] public string? ConfirmTime { get; set; }
            [Column("确认人")] public string? ConfirmPerson { get; set; }
            public int IsSchool { get; set; }
            [Column("质差数")] public int ErrCellCount { get; set; }

            // 添加无参数构造函数
            public ErrNode() { }

            public static ErrNode transtErrNodeFromDataGridViewRow(DataGridViewRow row)
            {
                return new ErrNode
                {
                    KeyId = row.SafeGetString("KeyId"),
                    ENI = row.SafeGetString("ENI"),
                    GcNodeName = row.SafeGetString("工参基站"),
                    StartDate = row.SafeGetString("开始日期"),
                    Problem = row.SafeGetString("问题分析"),
                    ProblemLevel1 = row.SafeGetString("问题一级"),
                    ProblemLevel2 = row.SafeGetString("问题二级"),
                    MeasureType = row.SafeGetString("措施分类"),
                    Measure = row.SafeGetString("处理措施"),
                    ResponsibleDept = row.SafeGetString("责任室"),
                    ResponsibleSpecialty = row.SafeGetString("责任专业"),
                    ResponsiblePerson = row.SafeGetString("责任人"),
                    ProcessTime = row.SafeGetString("处理日期"),
                    Status = row.SafeGetString("状态"),
                    ConfirmTime = row.SafeGetString("确认日期"),
                    ConfirmPerson = row.SafeGetString("确认人"),
                    IsSchool = row.SafeGetInt("IsSchool"),
                    ErrCellCount = row.SafeGetInt("质差数"),
                };
            }
        }

        [Table("ErrCell")]
        public sealed class ErrCell
        {
            // 完全保留原有属性定义
            [PrimaryKey]
            [Indexed] public string KeyId { get; set; } = null!;
            [Column("公司")] public string Company { get; set; } = null!;
            [Indexed] public string CGI { get; set; } = null!;
            [Indexed][Column("开始日期")] public string StartDate { get; set; } = null!;
            [Indexed][Column("结束日期")] public string EndDate { get; set; } = null!;
            [Column("持续天数")] public int DurationDays { get; set; } = 0;
            [Indexed] public string ENI { get; set; } = null!;
            [Indexed][Column("小区名")] public string CellName { get; set; } = null!;
            [Indexed][Column("工参小区")] public string? GcCellName { get; set; }
            [Column("质差名称")] public string ErrName { get; set; } = null!;
            [Column("质差类型")] public string ErrType { get; set; } = null!;
            public string Sys { get; set; } = null!;
            [Column("厂家")] public string Factory { get; set; } = null!;
            [Column("场景")] public string? Scene { get; set; }
            public int IsSchool { get; set; } = 0;
            public int IsWhite { get; set; } = 0;
            [Column("质差值")] public double ErrValue { get; set; } = 0;
            [Column("最新值")] public double LatestValue { get; set; } = 0;
            [Column("问题")] public string Problem { get; set; } = null!;
            [Column("责任室")] public string ResponsibleDept { get; set; } = null!;
            [Column("责任人")] public string ResponsiblePerson { get; set; } = null!;
            [Column("处理日期")] public string? ProcessTime { get; set; }
            [Column("状态")] public string Status { get; set; } = null!;

            // 添加无参数构造函数
            public ErrCell() { }

            // 扩展方法用于替代有约束的Table<T>()
            public static IEnumerable<ErrCell> QueryForType(SQLiteConnection db, string ErrName)
            {
                return db.Query<ErrCell>(
                    $"SELECT * FROM ErrCell WHERE ErrType = '{ErrName}'");
            }

            // 扩展方法用于获取最大日期
            public static string? GetMaxEndDate(SQLiteConnection db, string ErrName)
            {
                return db.ExecuteScalar<string?>(
                    $"SELECT MAX(EndDate) FROM ErrCell WHERE ErrType = '{ErrName}'");
            }

            public static ErrCell transtErrcellFromDataGridViewRow(DataGridViewRow row)
            {

                return new ErrCell
                {
                    KeyId = row.SafeGetString("KeyId"),
                    Company = row.SafeGetString("Company"),
                    CGI = row.SafeGetString("CGI"),
                    StartDate = row.SafeGetString("StartDate"),
                    EndDate = row.SafeGetString("EndDate"),
                    DurationDays = row.SafeGetInt("DurationDays"),
                    ENI = row.SafeGetString("ENI"),
                    CellName = row.SafeGetString("CellName"),
                    GcCellName = row.SafeGetString("GcCellName"),
                    ErrName = row.SafeGetString("ErrName"),
                    ErrType = row.SafeGetString("ErrType"),
                    Sys = row.SafeGetString("Sys"),
                    Factory = row.SafeGetString("Factory"),
                    Scene = row.SafeGetString("Scene"),
                    IsSchool = row.SafeGetInt("IsSchool"),
                    ErrValue = row.SafeGetDouble("ErrValue"),
                    LatestValue = row.SafeGetDouble("LatestValue"),
                    Problem = row.SafeGetString("Problem"),
                    ResponsibleDept = row.SafeGetString("ResponsibleDept"),
                    ResponsiblePerson = row.SafeGetString("ResponsiblePerson"),
                    ProcessTime = row.SafeGetString("ProcessTime"),
                    Status = row.SafeGetString("Status"),
                };
            }

            public static List<ErrCell> transtErrcellFromDataRows(DataTable dt, string ErrName, string tableName, int isVacation = 0)
            {
                var ErrCells = new List<ErrCell>();
                foreach (DataRow row in dt.Rows)
                {
                    ErrCells.Add(transtErrcellFromDataRow(row, ErrName, tableName, isVacation));
                }
                return ErrCells;
            }

            public static ErrCell transtErrcellFromDataRow(DataRow row, string ErrName, string tableName, int isVacation = 0)
            {
                //// 安全获取日期值
                //DateTime ParseDateSafe(string dateString)
                //{
                //    if (DateTime.TryParse(dateString, out DateTime result))
                //        return result;
                //    return DateTime.MinValue;
                //}

                int IsSchool = row.SafeGetInt("IsSchool");
                int IsWhite = row.SafeGetInt("IsWhite");
                string Problem = "待分析";
                string Status = "分析";



                string ColErrValue = string.Empty;
                switch (ErrName)
                {
                    case "4G低流量":
                        ColErrValue = "MB";
                        if (isVacation == 1 && IsSchool == 1) { Problem = "用户|放假"; Status = "观察"; }
                        break;
                    case "5G低流量":
                        ColErrValue = "MB";
                        if (isVacation == 1 && IsSchool == 1) { Problem = "用户|放假"; Status = "观察"; }
                        break;
                    case "5G低效益":
                        ColErrValue = "GbAvgMax3";
                        if (isVacation == 1 && IsWhite == 1 && IsSchool == 1) { Problem = "用户|放假"; Status = "观察"; }
                        else if (isVacation == 1 && IsWhite == 1 && IsSchool == 0) { Problem = "用户|潮汐场景"; Status = "观察"; }
                        break;
                }

                // 创建新的 ErrCell 对象
                string date = row.SafeGetString("date");
                return new ErrCell
                {
                    KeyId = $"{row["Date"]}_{row["CGI"]}_{tableName}", // 根据规则生成唯一Key
                    Company = row.SafeGetString("公司"),
                    CGI = row.SafeGetString("CGI"),
                    StartDate = date,
                    EndDate = date,
                    DurationDays = 1,
                    ENI = row.SafeGetString("ENI"),
                    CellName = row.SafeGetString("小区名"),
                    GcCellName = row.SafeGetString("工参小区"),
                    ErrName = ErrName,
                    ErrType = row.SafeGetString("质差类型"),
                    Sys = row.SafeGetString("Sys"),
                    Factory = row.SafeGetString("厂家"),
                    Scene = row.SafeGetString("场景"),
                    IsSchool = row.SafeGetInt("IsSchool"),
                    IsWhite = row.SafeGetInt("IsWhite"),
                    ErrValue = row.SafeGetDouble(ColErrValue),
                    LatestValue = 0,
                    Problem = Problem, // 默认值
                    ResponsibleDept = "待分配", // 默认值
                    ResponsiblePerson = "待分配", // 默认值
                    ProcessTime = null, // 新增记录无处理时间
                    Status = Status // 初始状态
                };
            }
        }

        [Table("ErrCellNodeRelation")]
        public sealed class ErrCellNodeRelation
        {
            [PrimaryKey]
            public string KeyId { get; set; } = null!;
            [Indexed] public string ErrCellKeyId { get; set; } = null!;
            [Indexed] public string ErrNodeKeyId { get; set; } = null!;


            // 添加无参数构造函数
            public ErrCellNodeRelation() { }
        }
        #endregion
    }
}
